const db = require('../../lib/conn_oracle')

exports.getProgressInfo = async (req, res) => {
    let { paramsForm } = req.body
    let { jcYear } = paramsForm
    // console.log(req.body)
    // console.log(jcYear)
    let resStatus
    let msg
    let resDatas = {
        overAllRes: [], // 整体进度
        lineProgressRes: [], // 线路进度
        PgProgressRes: [], // 班组进度
        monthProgressRes: [], // 月度进度
    }
    let jcyearWhere = `${jcYear}-12-31`
    let overAllSql = `with xf as (
                        SELECT count(x.wonum) as cx	
	                        ,sum(case when x.finishdate is not null then 1 else 0 end) as fcx  
                            ,sum(case when x.finishdate is null then 1 else 0 end) as nfcx
                        FROM C##WXW.XFJIANCE x
                        where x.status = 1 
                        and x.schedfinish = to_timestamp(:jcyearWhere,'yyyy-mm-dd hh24:mi:ss.ff')		        
                                )
                        -------------------------------------------------------------------------------------------
                        select xf.cx
                            ,xf.fcx
                            ,xf.nfcx	
                            ,round((xf.cx - xf.nfcx	)/xf.cx,4)*100 as wcl
                        from xf                    
                    `
    let lineProgressSql = `with xf as (
                            SELECT x.cust_linenum as linenum
                                ,count(x.wonum) as cx	
                                ,sum(case when x.finishdate is not null then 1 else 0 end) as fcx  
                                ,sum(case when x.finishdate is null then 1 else 0 end) as nfcx
                            FROM C##WXW.XFJIANCE x
                            where x.status = 1 
                            and x.schedfinish = to_timestamp(:jcyearWhere,'yyyy-mm-dd hh24:mi:ss.ff')		
                            group by x.cust_linenum        
                                    )
                            -------------------------------------------------------------------------------------------
                            select xf.linenum
                                ,xf.cx
                                ,xf.fcx
                                ,xf.nfcx	
                                ,round((xf.cx - xf.nfcx	)/xf.cx,4)*100 as wcl
                            from xf                    
                        `
    let PgProgressSql = `with xf as (
                            SELECT p.persongroup || '-' || p.pgname as pgname
                                ,count(x.wonum) as cx	
                                ,sum(case when x.finishdate is not null then 1 else 0 end) as fcx  
                                ,sum(case when x.finishdate is null then 1 else 0 end) as nfcx
                            FROM C##WXW.XFJIANCE x
                            inner join c##wxw.persongroup p
                            on x.persongroup = p.persongroup
                            where x.status = 1 
                            and x.schedfinish = to_timestamp(:jcyearWhere,'yyyy-mm-dd hh24:mi:ss.ff')		
                            group by p.persongroup || '-' || p.pgname   
                                    )
                            -------------------------------------------------------------------------------------------
                            select xf.pgname
                                ,xf.cx
                                ,xf.fcx
                                ,xf.nfcx	
                                ,round((xf.cx - xf.nfcx	)/xf.cx,4)*100 as wcl
                            from xf                    
                        `
    let monthProgressSql = `with xf as (
                            SELECT x.cust_linenum as linenum
                                ,to_char(x.finishdate,'yy-mm') as ym
                                ,count(x.wonum) as cx	
                                --,sum(case when x.finishdate is not null then 1 else 0 end) as fcx  
                                --,sum(case when x.finishdate is null then 1 else 0 end) as nfcx
                            FROM C##WXW.XFJIANCE x
                            where x.status = 1 
                            and x.schedfinish = to_timestamp(:jcyearWhere,'yyyy-mm-dd hh24:mi:ss.ff')
                            and x.finishdate is not null		
                            group by x.cust_linenum,to_char(x.finishdate,'yy-mm')                                  
                                    )
                            -------------------------------------------------------------------------------------------
                            select xf.linenum,xf.ym
                                ,xf.cx
                                --,xf.fcx
                                --,xf.nfcx	
                                -- ,round((xf.cx - xf.nfcx	)/xf.cx,4)*100 as wcl
                            from xf   
                            order by xf.linenum,xf.ym                 
                        `

    try {
        let jcRes = await db.search(overAllSql, [jcyearWhere])
        resDatas.overAllRes = jcRes
        let lineRes = await db.search(lineProgressSql, [jcyearWhere])
        resDatas.lineProgressRes = lineRes
        let pgRes = await db.search(PgProgressSql, [jcyearWhere])
        resDatas.PgProgressRes = pgRes
        let monthRes = await db.search(monthProgressSql, [jcyearWhere])
        resDatas.monthProgressRes = monthRes
        msg = resDatas.overAllRes.errorNum ? `整体进度查询失败` : `整体进度查询成功`
        msg = resDatas.lineProgressRes.errorNum ? `线路查询失败` : `线路查询成功`
        msg = resDatas.PgProgressRes.errorNum ? `班组查询失败` : `班组查询成功`
        msg = resDatas.monthProgressRes.errorNum? `月度进度查询失败` : `月度进度查询成功`
        resStatus = resDatas.overAllRes.errorNum ? 0 : 1
        resStatus = resDatas.lineProgressRes.errorNum ? 0 : 1
        resStatus = resDatas.PgProgressRes.errorNum ? 0 : 1
        resStatus = resDatas.monthProgressRes.errorNum? 0 : 1
        res.json({
            status: resStatus,
            msg,
            datas: resDatas,
        })
    } catch (err) {
        console.log(err)
        res.json({
            status: 0,
            msg: `查询失败`,
            datas: { err }
        })
    }
}

exports.getLocLinkageInfo = async (req, res) => {
    let { locParamsForm } = req.body
    let { jcYear , pg} = locParamsForm
    // console.log(locParamsForm)
    // console.log(jcYear)
    let resStatus
    let msg
    let resDatas 
    let jcyearWhere = `${jcYear}-12-31`
    let locProgressSql = `with xf as (
                            SELECT l.location || '-' || l.description as locdesc
                                ,count(x.wonum) as cx	
                                ,sum(case when x.finishdate is not null then 1 else 0 end) as fcx  
                                ,sum(case when x.finishdate is null then 1 else 0 end) as nfcx
                            FROM C##WXW.XFJIANCE x
                            inner join c##wxw.location l
                            on x.location = l.location
                            where x.status = 1 
                            and x.schedfinish = to_timestamp(:jcyearWhere,'yyyy-mm-dd hh24:mi:ss.ff')	
                            and x.persongroup = :pg	
                            group by l.location || '-' || l.description   
                                    )
                            -------------------------------------------------------------------------------------------
                            select xf.locdesc
                                ,xf.cx
                                ,xf.fcx
                                ,xf.nfcx	
                                ,round((xf.cx - xf.nfcx	)/xf.cx,4)*100 as wcl
                            from xf 
                            order by xf.locdesc  
                           `
    try {
        let locProgressRes = await db.search(locProgressSql, [jcyearWhere,pg])
        resDatas = {locProgressRes}
        msg = resDatas.locProgressRes.errorNum? `查询失败` : `查询成功`
        resStatus = resDatas.locProgressRes.errorNum? 0 : 1
        res.json({
            status: resStatus,
            msg,
            datas: resDatas,
        })
    }catch(err){
        console.log(err)
        res.json({
            status: 0,
            msg: `查询失败`,
            datas: { err }
        })
    }
}

exports.getSubLinkageInfo = async (req, res) => {
    let { subParamsForm } = req.body
    let { jcYear , station} = subParamsForm
    // console.log(subParamsForm)
    // console.log(jcYear)
    let resStatus
    let msg
    let resDatas 
    let jcyearWhere = `${jcYear}-12-31`
    let subProgressSql = `with xf as (
                            SELECT x.cust_subsys || '-' || s.sub_desc as subdesc
                                ,count(x.wonum) as cx	
                                ,sum(case when x.finishdate is not null then 1 else 0 end) as fcx  
                                ,sum(case when x.finishdate is null then 1 else 0 end) as nfcx
                            FROM C##WXW.XFJIANCE x
                            inner join c##wxw.subsys s
                            on x.cust_subsys = s.cust_subsys
                            where x.status = 1 
                            and x.schedfinish = to_timestamp(:jcyearWhere,'yyyy-mm-dd hh24:mi:ss.ff')	
                            and x.location = :station	
                            group by x.cust_subsys || '-' || s.sub_desc   
                                    )
                            -------------------------------------------------------------------------------------------
                            select xf.subdesc
                                ,xf.cx
                                ,xf.fcx
                                ,xf.nfcx	
                                ,round((xf.cx - xf.nfcx	)/xf.cx,4)*100 as wcl
                            from xf 
                            order by xf.subdesc  
                           `
    try {
        let subProgressRes = await db.search(subProgressSql, [jcyearWhere,station])
        resDatas = {subProgressRes}
        msg = resDatas.subProgressRes.errorNum? `查询失败` : `查询成功`
        resStatus = resDatas.subProgressRes.errorNum? 0 : 1
        res.json({
            status: resStatus,
            msg,
            datas: resDatas,
        })
    }catch(err){
        console.log(err)
        res.json({
            status: 0,
            msg: `查询失败`,
            datas: { err }
        })
    }
}

exports.getSubList = async (req, res) => {
    let { subListParamsForm } = req.body
    let { jcYear, station ,sub} = subListParamsForm
    // console.log(subListParamsForm)
    let resStatus
    let msg
    let resDatas = {subListRes:[]}
    let jcyearWhere = `${jcYear}-12-31`
    let subListSql = `select to_char(x.finishdate,'yyyy-mm-dd') as finishdate
                        ,count(x.wonum) as cx
                        from c##wxw.xfjiance x
                        where x.status = 1
                        and x.schedfinish = to_timestamp(:jcyearWhere,'yyyy-mm-dd hh24:mi:ss.ff')
                        and x.location = :station
                        and x.cust_subsys = :sub
                        and x.finishdate is not null
                        group by to_char(x.finishdate,'yyyy-mm-dd')
                        order by to_char(x.finishdate,'yyyy-mm-dd')
                        `
    try {
        let subListRes = await db.search(subListSql, [jcyearWhere,station,sub])
        resDatas.subListRes = subListRes
        msg = resDatas.subListRes.errorNum? `查询失败` : `查询成功`
        resStatus = resDatas.subListRes.errorNum? 0 : 1
        res.json({
            status: resStatus,
            msg,
            datas: resDatas,
        })
    }catch(err){
        console.log(err)
        res.json({
            status: 0,
            msg: `查询失败`,
            datas: { err }
        })
    }
}